package com.qf.dao.Impl;

import com.qf.dao.MusicDao;
import com.qf.pojo.Music;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.qf.util.DBUtils;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class MusicDaoImpl implements MusicDao {
    private QueryRunner queryRunner = new QueryRunner();

    /**
     * 找到所有
     *
     * @return {@link List}<{@link Music}>
     */
    @Override
    public List<Music> findAll() {
        try {
            DBUtils.begin();
            String sql = "select music_id as musicId,music_name as musicName," +
                    "music_album_name as musicAlbumName,music_album_picUrl as musicAlbumPicurl," +
                    "music_mp3Url as musicMp3url,music_artist_name as musicArtistName," +
                    "sheet_id as sheetId from tb_music";
            List<Music> list = queryRunner.query(DBUtils.getConnection(),
                    sql,
                    new BeanListHandler<>(Music.class));
            DBUtils.commit();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            DBUtils.rollback();
        }
        return null;
    }

    @Override
    public List<Music> findByName(String musicName) {
        try {
            DBUtils.begin();
            String sql = "select music_id as musicId,music_name as musicName," +
                    "music_album_name as musicAlbumName,music_album_picUrl as musicAlbumPicurl," +
                    "music_mp3Url as musicMp3url,music_artist_name as musicArtistName," +
                    "sheet_id as sheetId from tb_music where music_name like ?";
            List<Music> list = queryRunner.query(DBUtils.getConnection(),
                    sql,
                    new BeanListHandler<>(Music.class),
                    "%" + musicName + "%");
            DBUtils.commit();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            DBUtils.rollback();
        }
        return null;
    }

    @Override
    public Music findById(String musicId) {
        try {
            DBUtils.begin();
            String sql = "select music_id as musicId,music_name as musicName," +
                    "music_album_name as musicAlbumName,music_album_picUrl as musicAlbumPicurl," +
                    "music_mp3Url as musicMp3url,music_artist_name as musicArtistName," +
                    "sheet_id as sheetId from tb_music where music_id like ?";
            Music music = queryRunner.query(DBUtils.getConnection(),
                    sql,
                    new BeanHandler<>(Music.class),
                    "%" + musicId + "%");
            DBUtils.commit();
            return music;
        }catch (Exception e){
            e.printStackTrace();
            DBUtils.rollback();
        }
        return null;
    }

    @Override
    public List<Music> findByPage(String page, String limit) {
        try {
            DBUtils.begin();
            String sql = "select music_id as musicId,music_name as musicName," +
                    "music_album_name as musicAlbumName,music_album_picUrl as musicAlbumPicurl," +
                    "music_mp3Url as musicMp3url,music_artist_name as musicArtistName," +
                    "sheet_id as sheetId from tb_music limit ?,?";
            List<Music> musicList = queryRunner.query(DBUtils.getConnection(),
                    sql,
                    new BeanListHandler<>(Music.class),
                    (Integer.parseInt(page) - 1) * Integer.parseInt(limit), Integer.parseInt(limit));
            DBUtils.commit();
            return musicList;
        }catch (Exception e){
            e.printStackTrace();
            DBUtils.rollback();
        }
        return null;
    }

    @Override
    public Long queryTotalCount() {
        try {
            DBUtils.begin();
            String sql = "SELECT COUNT(*) FROM tb_music";
            Long count = (Long)queryRunner.query(DBUtils.getConnection(),
                    sql,
                    new ScalarHandler<>());
            DBUtils.commit();
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            DBUtils.rollback();
        }
        return null;
    }

    @Override
    public void add(Music music) {
        try {
            DBUtils.begin();
            String sql = "insert into tb_music(music_name,music_album_name,music_album_picUrl," +
                    "music_mp3Url,music_artist_name,sheet_id) values(?,?,?,?,?,?)";
            queryRunner.update(DBUtils.getConnection(),sql,music.getMusicName(),music.getMusicAlbumName(),
                    music.getMusicAlbumPicurl(),music.getMusicMp3url(),
                    music.getMusicArtistName(),music.getSheetId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
